<?php

namespace App\Helpers;

use Illuminate\Support\Facades\DB;
use App\Models\Company;
use Illuminate\Support\Str;

class DatabaseHelper
{
  public static function createStoredProcedures()
  {
    // 创建检查字段是否存在的存储过程
    DB::unprepared("
        DROP PROCEDURE IF EXISTS check_column_exists;
        CREATE PROCEDURE check_column_exists(
            IN p_table_name VARCHAR(64),
            IN p_column_name VARCHAR(64),
            OUT p_exists BOOLEAN
        )
        BEGIN
            DECLARE column_count INT;

            SELECT COUNT(*) INTO column_count
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
            AND TABLE_NAME = p_table_name
            AND COLUMN_NAME = p_column_name;

            SET p_exists = (column_count > 0);
        END;
    ");

    // 创建添加字段的存储过程
    DB::unprepared("
        DROP PROCEDURE IF EXISTS add_company_id_column;
        CREATE PROCEDURE add_company_id_column(IN p_table_name VARCHAR(64))
        BEGIN
            DECLARE column_exists BOOLEAN;

            CALL check_column_exists(p_table_name, 'company_id', column_exists);

            IF NOT column_exists THEN
                SET @sql = CONCAT('ALTER TABLE ', p_table_name,
                                 ' ADD COLUMN company_id INT COMMENT \"公司ID\" AFTER id');
                PREPARE stmt FROM @sql;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                SELECT CONCAT('表 ', p_table_name, ' 已成功添加 company_id 字段') AS result;
            ELSE
                SELECT CONCAT('表 ', p_table_name, ' 已存在 company_id 字段，无需添加') AS result;
            END IF;
        END;
    ");
  }

  public static function addCompanyIdToMultipleTables(array $tables)
  {
    $results = [];

    foreach ($tables as $table) {
      try {
        $result = DB::select(
          "CALL add_company_id_column(?)",
          [$table]
        );

        $company = Company::query()->first();
        if ($company) {
          DB::table($table)->update(['company_id' => $company->id]);
        }
        $results[$table] = $result[0]->result ?? '操作完成';
      } catch (\Exception $e) {
        $results[$table] = "操作失败: " . $e->getMessage();
      }
    }
    return $results;
  }

  public static function createTriggers($table, $from_table)
  {
    $fromId = Str::singular($from_table) . '_id';
    $newId = 'NEW.' . $fromId;
    $sql = '
    DROP TRIGGER IF EXISTS b4_' . $table . '_in_set_cid;
    CREATE TRIGGER b4_' . $table . '_in_set_cid
    BEFORE INSERT ON ' . $table . '
    FOR EACH ROW
    BEGIN
        SET NEW.company_id = (SELECT company_id
        FROM ' . $from_table . '
        WHERE id = ' . $newId . '
        LIMIT 1);
        IF NEW.company_id IS NULL THEN
            SET NEW.company_id = 0;
        END IF;
      END;';

    echo $sql;
    DB::unprepared($sql);

    // 可选：创建更新触发器（当families.staff_id被修改时）
    $sql = '
    DROP TRIGGER IF EXISTS b4_' . $table . '_up_set_cid;
    CREATE TRIGGER b4_' . $table . '_up_set_cid
    BEFORE UPDATE ON ' . $table . '
    FOR EACH ROW
    BEGIN
        IF NEW.' . $fromId . ' != OLD.' . $fromId . ' THEN
            SET NEW.company_id=(SELECT company_id
            FROM ' . $from_table . '
            WHERE id = ' . $newId . '
            LIMIT 1);
            IF NEW.company_id IS NULL THEN
                SET NEW.company_id = 0;
            END IF;
        END IF;
    END;';
    echo $sql;
    DB::unprepared($sql);
  }

  public static function alterUniqueIndex()
  {
    $sql = <<<SQL
        ALTER TABLE `bhcd_dorm`.`buildings` DROP INDEX `buildings_name_unique`;
        ALTER TABLE `bhcd_dorm`.`buildings` ADD UNIQUE INDEX(`company_id`, `name`) USING HASH;
        ALTER TABLE `bhcd_dorm`.`dorms` DROP INDEX `dorms_code_unique`;
        ALTER TABLE `bhcd_dorm`.`dorms` ADD UNIQUE INDEX(`company_id`, `code`) USING HASH;
        ALTER TABLE `bhcd_dorm`.`departments` DROP INDEX `departments_name_unique`;
        ALTER TABLE `bhcd_dorm`.`departments` ADD UNIQUE INDEX(`company_id`, `name`) USING HASH;
        ALTER TABLE `bhcd_dorm`.`dorm_meters` DROP INDEX `dorm_meters_nfc_id_unique`;
        ALTER TABLE `bhcd_dorm`.`dorm_meters` ADD UNIQUE INDEX(`company_id`, `nfc_id`) USING HASH;
        ALTER TABLE `bhcd_dorm`.`staff_positions` DROP INDEX `staff_positions_position_unique`;
        ALTER TABLE `bhcd_dorm`.`staff_positions` ADD UNIQUE INDEX(`company_id`, `position`) USING HASH;
        ALTER TABLE `bhcd_dorm`.`dorm_types` DROP INDEX `dorm_types_name_unique`;
        ALTER TABLE `bhcd_dorm`.`dorm_types` ADD UNIQUE INDEX(`company_id`, `name`) USING HASH;
        ALTER TABLE `dorms`DROP INDEX `dorms_code_unique` ,ADD UNIQUE INDEX `dorms_code_unique` (`company_id`, `code`) USING BTREE ;
SQL;

    try {
      DB::unprepared($sql);
    } catch (\Exception $e) {
      // 记录异常信息
      \Log::error("Failed to alter unique index: " . $e->getMessage());
    }
  }
}
